﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using DTO;

namespace DAO
{
    public class ThongKeDAO
    {
        public List<ThongKeDTO> ThongKe(DateTime tungay, DateTime denngay)
        {
            string sqlStr = "select mh.MaMatHang, mh.TenMatHang, mh.MaXuatXu, sum(cthd.SoLuong), sum(cthd.SoLuong*cthd.DonGia) ";
            sqlStr = sqlStr + "from CHITIETHOADON cthd join HOADON hd on cthd.MaHoaDon = hd.MaHoaDon join ";
            sqlStr = sqlStr + "MATHANG mh on cthd.MaMatHang = mh.MaMatHang and cthd.MaXuatXu = mh.MaXuatXu ";
            sqlStr = sqlStr + "and datediff(d,hd.NgayLap,@tungay) <= 0 and datediff(d,hd.NgayLap,@denngay) >= 0 ";
            sqlStr = sqlStr + "group by mh.MaMatHang, mh.TenMatHang, mh.MaXuatXu ";
            sqlStr = sqlStr + "order by sum(cthd.SoLuong*cthd.DonGia) desc";

            SqlConnection cn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand(sqlStr, cn);
            cm.Parameters.Add("@tungay", SqlDbType.Date).Value = tungay;
            cm.Parameters.Add("@denngay", SqlDbType.Date).Value = denngay;
            SqlDataReader dr = cm.ExecuteReader();
            List<ThongKeDTO> ds = new List<ThongKeDTO>();

            while (dr.Read())
            {
                ThongKeDTO tkDTO = new ThongKeDTO();
                tkDTO.MaMatHang = dr.GetInt32(0);
                tkDTO.TenMatHang = dr.GetString(1);
                tkDTO.MaXuatXu = dr.GetInt32(2);
                tkDTO.SoLuongBan = dr.GetInt32(3);
                tkDTO.DoanhThu = dr.GetDouble(4);
                ds.Add(tkDTO);
            }

            dr.Close();
            cn.Close();
            return ds;
        }
    }
}
